/*==============================================================*/
/* DBMS name:      Sybase AS Enterprise 15.0.2                  */
/* Created on:     15/05/2013 10:15:12 p.m.                     */
/*==============================================================*/


if exists (select 1 
            from  sysobjects c 
            join  sysconstraints s on (s.constrid = c.id and s.tableid = object_id('ASIGNATURA'))
            where name = 'FK_ASIGNATU_INS_ASI_INSCRIPC' and type = 'RI')
   alter table ASIGNATURA
      drop constraint FK_ASIGNATU_INS_ASI_INSCRIPC
go

if exists (select 1 
            from  sysobjects c 
            join  sysconstraints s on (s.constrid = c.id and s.tableid = object_id('CAR_PRO'))
            where name = 'FK_CAR_PRO_CAR_PRO_CAR_ACA' and type = 'RI')
   alter table CAR_PRO
      drop constraint FK_CAR_PRO_CAR_PRO_CAR_ACA
go

if exists (select 1 
            from  sysobjects c 
            join  sysconstraints s on (s.constrid = c.id and s.tableid = object_id('CAR_PRO'))
            where name = 'FK_CAR_PRO_CAR_PRO2_PROFESOR' and type = 'RI')
   alter table CAR_PRO
      drop constraint FK_CAR_PRO_CAR_PRO2_PROFESOR
go

if exists (select 1 
            from  sysobjects c 
            join  sysconstraints s on (s.constrid = c.id and s.tableid = object_id('GRUPO'))
            where name = 'FK_GRUPO_ASIGNATUR_ASIGNATU' and type = 'RI')
   alter table GRUPO
      drop constraint FK_GRUPO_ASIGNATUR_ASIGNATU
go

if exists (select 1 
            from  sysobjects c 
            join  sysconstraints s on (s.constrid = c.id and s.tableid = object_id('GRUPO'))
            where name = 'FK_GRUPO_GRUPO_CAR_CAR_ACA' and type = 'RI')
   alter table GRUPO
      drop constraint FK_GRUPO_GRUPO_CAR_CAR_ACA
go

if exists (select 1 
            from  sysobjects c 
            join  sysconstraints s on (s.constrid = c.id and s.tableid = object_id('GRUPO_ESPACIO'))
            where name = 'FK_GRUPO_ES_GRUPO_ESP_GRUPO' and type = 'RI')
   alter table GRUPO_ESPACIO
      drop constraint FK_GRUPO_ES_GRUPO_ESP_GRUPO
go

if exists (select 1 
            from  sysobjects c 
            join  sysconstraints s on (s.constrid = c.id and s.tableid = object_id('GRUPO_ESPACIO'))
            where name = 'FK_GRUPO_ES_GRUPO_ESP_ESPACIOF' and type = 'RI')
   alter table GRUPO_ESPACIO
      drop constraint FK_GRUPO_ES_GRUPO_ESP_ESPACIOF
go

if exists (select 1 
            from  sysobjects c 
            join  sysconstraints s on (s.constrid = c.id and s.tableid = object_id('INSCRI_ESTU'))
            where name = 'FK_INSCRI_E_INSCRI_ES_INSCRIPC' and type = 'RI')
   alter table INSCRI_ESTU
      drop constraint FK_INSCRI_E_INSCRI_ES_INSCRIPC
go

if exists (select 1 
            from  sysobjects c 
            join  sysconstraints s on (s.constrid = c.id and s.tableid = object_id('INSCRI_ESTU'))
            where name = 'FK_INSCRI_E_INSCRI_ES_ESTUDIAN' and type = 'RI')
   alter table INSCRI_ESTU
      drop constraint FK_INSCRI_E_INSCRI_ES_ESTUDIAN
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('ASIGNATURA')
            and   name  = 'INS_ASI_FK'
            and   indid > 0
            and   indid < 255)
   drop index ASIGNATURA.INS_ASI_FK
go

if exists (select 1
            from  sysobjects
            where id = object_id('ASIGNATURA')
            and   type = 'U')
   drop table ASIGNATURA
go

if exists (select 1
            from  sysobjects
            where id = object_id('BITACORA')
            and   type = 'U')
   drop table BITACORA
go

if exists (select 1
            from  sysobjects
            where id = object_id('CAR_ACA')
            and   type = 'U')
   drop table CAR_ACA
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('CAR_PRO')
            and   name  = 'CAR_PRO2_FK'
            and   indid > 0
            and   indid < 255)
   drop index CAR_PRO.CAR_PRO2_FK
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('CAR_PRO')
            and   name  = 'CAR_PRO_FK'
            and   indid > 0
            and   indid < 255)
   drop index CAR_PRO.CAR_PRO_FK
go

if exists (select 1
            from  sysobjects
            where id = object_id('CAR_PRO')
            and   type = 'U')
   drop table CAR_PRO
go

if exists (select 1
            from  sysobjects
            where id = object_id('CONTROL_B')
            and   type = 'U')
   drop table CONTROL_B
go

if exists (select 1
            from  sysobjects
            where id = object_id('ESPACIOFISICO')
            and   type = 'U')
   drop table ESPACIOFISICO
go

if exists (select 1
            from  sysobjects
            where id = object_id('ESTUDIANTE')
            and   type = 'U')
   drop table ESTUDIANTE
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('GRUPO')
            and   name  = 'GRUPO_CAR_FK'
            and   indid > 0
            and   indid < 255)
   drop index GRUPO.GRUPO_CAR_FK
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('GRUPO')
            and   name  = 'ASIGNATURA_GRUPO_FK'
            and   indid > 0
            and   indid < 255)
   drop index GRUPO.ASIGNATURA_GRUPO_FK
go

if exists (select 1
            from  sysobjects
            where id = object_id('GRUPO')
            and   type = 'U')
   drop table GRUPO
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('GRUPO_ESPACIO')
            and   name  = 'GRUPO_ESPACIO2_FK'
            and   indid > 0
            and   indid < 255)
   drop index GRUPO_ESPACIO.GRUPO_ESPACIO2_FK
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('GRUPO_ESPACIO')
            and   name  = 'GRUPO_ESPACIO_FK'
            and   indid > 0
            and   indid < 255)
   drop index GRUPO_ESPACIO.GRUPO_ESPACIO_FK
go

if exists (select 1
            from  sysobjects
            where id = object_id('GRUPO_ESPACIO')
            and   type = 'U')
   drop table GRUPO_ESPACIO
go

if exists (select 1
            from  sysobjects
            where id = object_id('INSCRIPCION')
            and   type = 'U')
   drop table INSCRIPCION
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('INSCRI_ESTU')
            and   name  = 'INSCRI_ESTU2_FK'
            and   indid > 0
            and   indid < 255)
   drop index INSCRI_ESTU.INSCRI_ESTU2_FK
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('INSCRI_ESTU')
            and   name  = 'INSCRI_ESTU_FK'
            and   indid > 0
            and   indid < 255)
   drop index INSCRI_ESTU.INSCRI_ESTU_FK
go

if exists (select 1
            from  sysobjects
            where id = object_id('INSCRI_ESTU')
            and   type = 'U')
   drop table INSCRI_ESTU
go

if exists (select 1
            from  sysobjects
            where id = object_id('PROFESOR')
            and   type = 'U')
   drop table PROFESOR
go

/*==============================================================*/
/* Table: ASIGNATURA                                            */
/*==============================================================*/
create table ASIGNATURA (
   ASI_ID               int                            not null,
   ASI_NOMBRE           varchar(20)                    not null,
   INS_ID               int                            not null,
   ASI_HORA             time                           not null,
   ASI_CREDITOS         numeric(1)                     not null,
   ASI_TIPO             varchar(256)                   not null,
   constraint PK_ASIGNATURA primary key nonclustered (ASI_ID, ASI_NOMBRE)
)
go

/*==============================================================*/
/* Index: INS_ASI_FK                                            */
/*==============================================================*/
create index INS_ASI_FK on ASIGNATURA (
INS_ID ASC
)
go

/*==============================================================*/
/* Table: BITACORA                                              */
/*==============================================================*/
create table BITACORA (
   BIT_ID               int                            not null,
   BIT_TABLA            varchar(20)                    not null,
   BIT_OPERACION        varchar(10)                    not null,
   BIT_USUARIO          varchar(30)                    not null,
   BIT_MAQUINA          varchar(50)                    not null,
   BIT_FECHA            datetime                       not null,
   BIT_DATOS            varchar(50)                    not null,
   constraint PK_BITACORA primary key nonclustered (BIT_ID)
)
go

/*==============================================================*/
/* Table: CAR_ACA                                               */
/*==============================================================*/
create table CAR_ACA (
   CAR_ID               int                            not null,
   CAR_ANO              date                           not null,
   CAR_SEMES            date                           not null,
   constraint PK_CAR_ACA primary key nonclustered (CAR_ID)
)
go

/*==============================================================*/
/* Table: CAR_PRO                                               */
/*==============================================================*/
create table CAR_PRO (
   CAR_ID               int                            not null,
   PRO_ID               int                            not null,
   constraint PK_CAR_PRO primary key (CAR_ID, PRO_ID)
)
go

/*==============================================================*/
/* Index: CAR_PRO_FK                                            */
/*==============================================================*/
create index CAR_PRO_FK on CAR_PRO (
CAR_ID ASC
)
go

/*==============================================================*/
/* Index: CAR_PRO2_FK                                           */
/*==============================================================*/
create index CAR_PRO2_FK on CAR_PRO (
PRO_ID ASC
)
go

/*==============================================================*/
/* Table: CONTROL_B                                             */
/*==============================================================*/
create table CONTROL_B (
   CRTL_ID              int                            not null,
   CRTL_OPERACION       varchar(10)                    not null,
   CRTL_USUARIO         varchar(30)                    not null,
   CRTL_MAQUINA         varchar(50)                    not null,
   CRTL_FECHA           datetime                       not null
)
go

/*==============================================================*/
/* Table: ESPACIOFISICO                                         */
/*==============================================================*/
create table ESPACIOFISICO (
   ESP_ID               int                            not null,
   ESP_NOMBRE           varchar(50)                    not null,
   constraint PK_ESPACIOFISICO primary key nonclustered (ESP_ID, ESP_NOMBRE)
)
go

/*==============================================================*/
/* Table: ESTUDIANTE                                            */
/*==============================================================*/
create table ESTUDIANTE (
   ESTU_ID              int                            not null,
   ESTU_NOMBRES         varchar(50)                    not null,
   ESTU_APELLIDOS       varchar(50)                    not null,
   ESTU_TELEFONO        numeric(7)                     not null,
   ESTU_TELEFONO2        VARCHAR(10)                   NULL,
   ESTU_TELEFONO3        VARCHAR(10)          	       NULL,
   ESTU_TELEFONO4        VARCHAR(10)                   NULL,
   ESTU_TELEFONO5        VARCHAR(10)                   NULL,
   ESTU_GENERO          varchar(10)                    not null,
   ESTU_CIVIL           CHAR(1)                        NOT NULL,
   ESTU_DIRECCION       varchar(20)                    not null,
   ESTU_CELULAR         numeric(10)                    not null,
   ESTU_NACIMIENTO      date                           not null,
   ESTU_INGRESOU        date                           not null,
   ESTU_CODIGO          int                            not null,
   ESTU_PROGRAMA        varchar(20)                    not null,
   constraint PK_ESTUDIANTE primary key nonclustered (ESTU_ID, ESTU_CODIGO)
)
go

ALTER TABLE ESTUDIANTE
   ADD CONSTRAINT CKC_ESTU_NACIMIENTO CHECK (((ESTU_NACIMIENTO < DATEADD(YEAR, -12, GETDATE())) AND (ESTU_NACIMIENTO < ESTU_INGRESOU)))
go

ALTER TABLE ESTUDIANTE
   ADD CONSTRAINT CKC_ESTU_GENERO CHECK (ESTU_GENERO IN ('M','F'))
go

ALTER TABLE ESTUDIANTE
   ADD CONSTRAINT CKC_ESTU_CIVIL CHECK (ESTU_CIVIL IN ('C','S','U','D','V'))
go

ALTER TABLE ESTUDIANTE
   ADD CONSTRAINT CKC_ESTU_TELEFONO CHECK (ESTU_TELEFONO LIKE '[5][7][1,2,4,5,6,7,8][0-9][0-9][0-9][0-9][0-9][0-9][0-9]')
go

ALTER TABLE ESTUDIANTE
   ADD CONSTRAINT CKC_ESTU_CELULAR CHECK (((SUBSTRING(ESTU_CELULAR, 1, 3) = '300' OR
														      SUBSTRING(ESTU_CELULAR, 1, 3) = '301' OR
														      SUBSTRING(ESTU_CELULAR, 1, 3) = '302' OR
														      SUBSTRING(ESTU_CELULAR, 1, 3) = '310' OR
														      SUBSTRING(ESTU_CELULAR, 1, 3) = '311' OR
														      SUBSTRING(ESTU_CELULAR, 1, 3) = '312' OR
														      SUBSTRING(ESTU_CELULAR, 1, 3) = '315' OR
														      SUBSTRING(ESTU_CELULAR, 1, 3) = '316') AND
														     (CONVERT(INTEGER, SUBSTRING(ESTU_CELULAR, 4, 10)) BETWEEN 0 AND 9999999)))
go

ALTER TABLE ESTUDIANTE
   ADD CONSTRAINT CKC_ESTU_TELEFONO2 CHECK (ESTU_TELEFONO2 IS NULL OR (ESTU_TELEFONO2 LIKE '[5][7][1,2,4,5,6,7,8][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'))
go

ALTER TABLE ESTUDIANTE
   ADD CONSTRAINT CKC_ESTU_TELEFONO3 CHECK (ESTU_TELEFONO3 IS NULL OR (ESTU_TELEFONO3 LIKE '[5][7][1,2,4,5,6,7,8][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'))
go

ALTER TABLE ESTUDIANTE
   ADD CONSTRAINT CKC_ESTU_TELEFONO4 CHECK (ESTU_TELEFONO4 IS NULL OR (ESTU_TELEFONO4 LIKE '[5][7][1,2,4,5,6,7,8][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'))
go

ALTER TABLE ESTUDIANTE
   ADD CONSTRAINT CKC_ESTU_TELEFONO5 CHECK (ESTU_TELEFONO5 IS NULL OR (ESTU_TELEFONO5 LIKE '[5][7][1,2,4,5,6,7,8][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'))
go

/*==============================================================*/
/* Table: GRUPO                                                 */
/*==============================================================*/
create table GRUPO (
   GRUPO_ID             int                            not null,
   CAR_ID               int                            not null,
   ASI_ID               int                            not null,
   ASI_NOMBRE           varchar(20)                    not null,
   constraint PK_GRUPO primary key nonclustered (GRUPO_ID)
)
go

/*==============================================================*/
/* Index: ASIGNATURA_GRUPO_FK                                   */
/*==============================================================*/
create index ASIGNATURA_GRUPO_FK on GRUPO (
ASI_ID ASC,
ASI_NOMBRE ASC
)
go

/*==============================================================*/
/* Index: GRUPO_CAR_FK                                          */
/*==============================================================*/
create index GRUPO_CAR_FK on GRUPO (
CAR_ID ASC
)
go

/*==============================================================*/
/* Table: GRUPO_ESPACIO                                         */
/*==============================================================*/
create table GRUPO_ESPACIO (
   GRUPO_ID             int                            not null,
   ESP_ID               int                            not null,
   ESP_NOMBRE           varchar(50)                    not null,
   constraint PK_GRUPO_ESPACIO primary key (GRUPO_ID, ESP_ID, ESP_NOMBRE)
)
go

/*==============================================================*/
/* Index: GRUPO_ESPACIO_FK                                      */
/*==============================================================*/
create index GRUPO_ESPACIO_FK on GRUPO_ESPACIO (
GRUPO_ID ASC
)
go

/*==============================================================*/
/* Index: GRUPO_ESPACIO2_FK                                     */
/*==============================================================*/
create index GRUPO_ESPACIO2_FK on GRUPO_ESPACIO (
ESP_ID ASC,
ESP_NOMBRE ASC
)
go

/*==============================================================*/
/* Table: INSCRIPCION                                           */
/*==============================================================*/
create table INSCRIPCION (
   INS_ID               int                            not null,
   INS_ANO              date                           not null,
   INS_SEMES            date                           not null,
   INS_NOTA             numeric(2)                     not null
         constraint CKC_INS_NOTA_INSCRIPC check (INS_NOTA is null or (INS_NOTA between 0 and 50)),
   constraint PK_INSCRIPCION primary key nonclustered (INS_ID)
)
go

/*==============================================================*/
/* Table: INSCRI_ESTU                                           */
/*==============================================================*/
create table INSCRI_ESTU (
   INS_ID               int                            not null,
   ESTU_ID              int                            not null,
   ESTU_CODIGO          int                            not null,
   constraint PK_INSCRI_ESTU primary key (INS_ID, ESTU_ID, ESTU_CODIGO)
)
go

/*==============================================================*/
/* Index: INSCRI_ESTU_FK                                        */
/*==============================================================*/
create index INSCRI_ESTU_FK on INSCRI_ESTU (
INS_ID ASC
)
go

/*==============================================================*/
/* Index: INSCRI_ESTU2_FK                                       */
/*==============================================================*/
create index INSCRI_ESTU2_FK on INSCRI_ESTU (
ESTU_ID ASC,
ESTU_CODIGO ASC
)
go

/*==============================================================*/
/* Table: PROFESOR                                              */
/*==============================================================*/
create table PROFESOR (
   PRO_ID               int                            not null,
   PRO_NOMBRES          varchar(50)                    not null,
   PRO_CIVIL            CHAR(1)                        NOT NULL,
   PRO_APELLIDOS        varchar(50)                    not null,
   PRO_SUELDO           money                          null,
   PRO_TELEFONO         numeric(10)                    not null,
   PRO_TELEFONO2        VARCHAR(10)                    NULL,
   PRO_TELEFONO3        VARCHAR(10)          	       NULL,
   PRO_TELEFONO4        VARCHAR(10)                    NULL,
   PRO_TELEFONO5        VARCHAR(10)                    NULL,
   PRO_CELULAR          numeric(10)                    not null,
   PRO_DIRECCION        varchar(20)                    null,
   PRO_NACIMIENTO       date                           not null,
   PRO_GENERO           varchar(1)                     not null,
   PRO_GRADO            date                           not null,
   PRO_INGRESOU         date                           not null,
   constraint PK_PROFESOR primary key nonclustered (PRO_ID)
)
go

alter table ASIGNATURA
   add constraint FK_ASIGNATU_INS_ASI_INSCRIPC foreign key (INS_ID)
      references INSCRIPCION (INS_ID)
go

alter table CAR_PRO
   add constraint FK_CAR_PRO_CAR_PRO_CAR_ACA foreign key (CAR_ID)
      references CAR_ACA (CAR_ID)
go

alter table CAR_PRO
   add constraint FK_CAR_PRO_CAR_PRO2_PROFESOR foreign key (PRO_ID)
      references PROFESOR (PRO_ID)
go

alter table GRUPO
   add constraint FK_GRUPO_ASIGNATUR_ASIGNATU foreign key (ASI_ID, ASI_NOMBRE)
      references ASIGNATURA (ASI_ID, ASI_NOMBRE)
go

alter table GRUPO
   add constraint FK_GRUPO_GRUPO_CAR_CAR_ACA foreign key (CAR_ID)
      references CAR_ACA (CAR_ID)
go

alter table GRUPO_ESPACIO
   add constraint FK_GRUPO_ES_GRUPO_ESP_GRUPO foreign key (GRUPO_ID)
      references GRUPO (GRUPO_ID)
go

alter table GRUPO_ESPACIO
   add constraint FK_GRUPO_ES_GRUPO_ESP_ESPACIOF foreign key (ESP_ID, ESP_NOMBRE)
      references ESPACIOFISICO (ESP_ID, ESP_NOMBRE)
go

alter table INSCRI_ESTU
   add constraint FK_INSCRI_E_INSCRI_ES_INSCRIPC foreign key (INS_ID)
      references INSCRIPCION (INS_ID)
go

alter table INSCRI_ESTU
   add constraint FK_INSCRI_E_INSCRI_ES_ESTUDIAN foreign key (ESTU_ID, ESTU_CODIGO)
      references ESTUDIANTE (ESTU_ID, ESTU_CODIGO)

go

ALTER TABLE PROFESOR
   ADD CONSTRAINT CKC_PRO_NACIMIENTO CHECK (((PRO_NACIMIENTO < DATEADD(YEAR, -23, GETDATE()))AND (PRO_NACIMIENTO < PRO_GRADO)))
go

ALTER TABLE PROFESOR
   ADD CONSTRAINT CKC_PRO_INGRESOU CHECK (((PRO_INGRESOU > DATEADD(YEAR,4,PRO_GRADO))AND (PRO_INGRESOU <= GETDATE())))
go

ALTER TABLE PROFESOR
   ADD CONSTRAINT CKC_PRO_GENERO CHECK (PRO_GENERO IN ('M','F'))
go

ALTER TABLE PROFESOR
   ADD CONSTRAINT CKC_PRO_CIVIL CHECK (PRO_CIVIL IN ('C','S','U','D','V'))
go

ALTER TABLE PROFESOR
   ADD CONSTRAINT CKC_PRO_TELEFONO CHECK (PRO_TELEFONO LIKE '[5][7][1,2,4,5,6,7,8][0-9][0-9][0-9][0-9][0-9][0-9][0-9]')
go

ALTER TABLE PROFESOR
   ADD CONSTRAINT CKC_PRO_CELULAR CHECK (((SUBSTRING(PRO_CELULAR, 1, 3) = '300' OR
														      SUBSTRING(PRO_CELULAR, 1, 3) = '301' OR
														      SUBSTRING(PRO_CELULAR, 1, 3) = '302' OR
														      SUBSTRING(PRO_CELULAR, 1, 3) = '310' OR
														      SUBSTRING(PRO_CELULAR, 1, 3) = '311' OR
														      SUBSTRING(PRO_CELULAR, 1, 3) = '312' OR
														      SUBSTRING(PRO_CELULAR, 1, 3) = '315' OR
														      SUBSTRING(PRO_CELULAR, 1, 3) = '316') AND
														     (CONVERT(INTEGER, SUBSTRING(PRO_CELULAR, 4, 10)) BETWEEN 0 AND 9999999)))
go

ALTER TABLE PROFESOR
   ADD CONSTRAINT CKC_PRO_TELEFONO2 CHECK (PRO_TELEFONO2 IS NULL OR (PRO_TELEFONO2 LIKE '[5][7][1,2,4,5,6,7,8][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'))
go

ALTER TABLE PROFESOR
   ADD CONSTRAINT CKC_PRO_TELEFONO3 CHECK (PRO_TELEFONO3 IS NULL OR (PRO_TELEFONO3 LIKE '[5][7][1,2,4,5,6,7,8][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'))
go

ALTER TABLE PROFESOR
   ADD CONSTRAINT CKC_PRO_TELEFONO4 CHECK (PRO_TELEFONO4 IS NULL OR (PRO_TELEFONO4 LIKE '[5][7][1,2,4,5,6,7,8][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'))
go

ALTER TABLE PROFESOR
   ADD CONSTRAINT CKC_PRO_TELEFONO5 CHECK (PRO_TELEFONO5 IS NULL OR (PRO_TELEFONO5 LIKE '[5][7][1,2,4,5,6,7,8][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'))
go

ALTER TABLE PROFESOR
   ADD CONSTRAINT CKC_PRO_GRADO CHECK (((PRO_GRADO < GETDATE())AND
													    (PRO_FECHA1 < PRO_ENTRA)))
GO

/*==============================================================*/
/* VIEW: V_ASIGNATURA                                           */
/*==============================================================*/
CREATE VIEW V_ASIGNATURA AS
SELECT
   ASIGNATURA.ASI_ID,
   ASIGNATURA.GRP_ID,
   ASIGNATURA.ASI_NOMBRE,
   ASIGNATURA.ASI_HORAS,
   ASIGNATURA.ASI_CREDITOS,
   ASIGNATURA.ASI_TIPO
FROM
   ASIGNATURA
GO

/*==============================================================*/
/* VIEW: V_BITACORA                                             */
/*==============================================================*/
CREATE VIEW V_BITACORA AS
SELECT
   BITACORA.BIT_ID,
   BITACORA.BIT_TABLA,
   BITACORA.BIT_OPERACION,
   BITACORA.BIT_USUARIO,
   BITACORA.BIT_MAQUINA,
   BITACORA.BIT_FECHA,
   BITACORA.BIT_DATOS
FROM
   BITACORA
GO

/*==============================================================*/
/* VIEW: V_ESTUDIANTE                                           */
/*==============================================================*/
CREATE VIEW V_ESTUDIANTE AS
SELECT
   ESTUDIANTE.ESTU_ID,
   ESTUDIANTE.ESTU_NOMBRES,
   ESTUDIANTE.ESTU_NACIMIENTO,
   ESTUDIANTE.ESTU_DIRECCION,
   ESTUDIANTE.ESTU_INGRESOU,
   ESTUDIANTE.ESTU_GENERO,
   ESTUDIANTE.ESTU_CIVIL,
   ESTUDIANTE.ESTU_TELEFONO
   
FROM
   ESTUDIANTE
GO

/*==============================================================*/
/* VIEW: V_INSCRIPCION                                          */
/*==============================================================*/
CREATE VIEW V_INSCRIPCION AS
SELECT
   INSCRIPCION.INS_ID,
   INSCRIPCION.INS_NOTA,
   INSCRIPCION.INS_CONCEPTO,
   INSCRIPCION.INS_SEMESTRE,
   INSCRIPCION.INS_ANO
FROM
   INSCRIPCION
GO

/*==============================================================*/
/* VIEW: V_INSC_EST                                             */
/*==============================================================*/
CREATE VIEW V_INSC_EST AS
SELECT
   INSC_EST.INS_ID,
   INSC_EST.EST_ID
FROM
   INSC_EST
GO


CREATE TRIGGER TR_ASIGNATURA ON ASIGNATURA
FOR INSERT, UPDATE, DELETE
AS
	--DETERMINE IF THIS IS AN INSERT,UPDATE, OR DELETE ACTION OR A "FAILED DELETE".
    DECLARE @ACTION CHAR(1)
    DECLARE @THIS_TABLE VARCHAR(20)
    DECLARE @USER_MACHINE VARCHAR(255)
    SELECT @THIS_TABLE = 'ASIGNATURA'
    SELECT @USER_MACHINE = HOST_NAME()
    
    SET @ACTION = (CASE WHEN EXISTS(SELECT * FROM inserted)
                         AND EXISTS(SELECT * FROM deleted)
                            THEN 'U'  -- SET ACTION TO UPDATED.
                        WHEN EXISTS(SELECT * FROM inserted)
                            THEN 'I'  -- SET ACTION TO INSERT.
                        WHEN EXISTS(SELECT * FROM deleted)
                            THEN 'D'  -- SET ACTION TO deleted.
                        ELSE NULL -- SKIP. IT MAY HAVE BEEN A "FAILED DELETE".   
                    END)
                    
     IF (@ACTION = 'I')
     BEGIN
        DECLARE @VARID VARCHAR(12)
	    SELECT @VARID = (SELECT ASI_ID FROM inserted)
        INSERT INTO BITACORA VALUES(@THIS_TABLE,@ACTION,USER_NAME(),@USER_MACHINE,GETDATE(),@VARID)
     END
     
     IF (@ACTION = 'U')
     BEGIN
        DECLARE C CURSOR  FOR
        SELECT ASI_ID
        FROM inserted

    	DECLARE @VARID1 VARCHAR(12)

    	-- OPEN THE CURSOR
    	OPEN C

    	FETCH NEXT FROM C INTO @VARID1
    	WHILE (@@FETCH_STATUS = 0)
    	BEGIN
    		 INSERT INTO BITACORA VALUES(@THIS_TABLE,@ACTION,USER_NAME(),@USER_MACHINE,GETDATE(),@VARID1)

    		-- DO OTHER STUFF

    		FETCH NEXT FROM C INTO @VARID1
    	END

    	-- CLOSE AND DEALLOCATE THE CURSOR
    	CLOSE C
    	DEALLOCATE C
     END
     
     
     IF (@ACTION = 'D')
     BEGIN
        DECLARE C CURSOR  FOR
        SELECT ASI_ID
        FROM deleted

    	DECLARE @VARID2 VARCHAR(12)

    	-- OPEN THE CURSOR
    	OPEN C

    	FETCH NEXT FROM C INTO @VARID2
    	WHILE (@@FETCH_STATUS = 0)
    	BEGIN
    		 INSERT INTO BITACORA VALUES(@THIS_TABLE,@ACTION,USER_NAME(),@USER_MACHINE,GETDATE(),@VARID2)

    		-- DO OTHER STUFF

    		FETCH NEXT FROM C INTO @VARID2
    	END

    	-- CLOSE AND DEALLOCATE THE CURSOR
    	CLOSE C
    	DEALLOCATE C
     END

GO


CREATE TRIGGER TR_BITACORA ON BITACORA
FOR INSERT, UPDATE, DELETE
AS
	--DETERMINE IF THIS IS AN INSERT,UPDATE, OR DELETE ACTION OR A "FAILED DELETE".
    DECLARE @ACTION CHAR(1)
    DECLARE @THIS_TABLE VARCHAR(20)
    DECLARE @USER_MACHINE VARCHAR(255)
    SELECT @THIS_TABLE = 'CARGA'
    SELECT @USER_MACHINE = HOST_NAME()
    
    SET @ACTION = (CASE WHEN EXISTS(SELECT * FROM inserted)
                         AND EXISTS(SELECT * FROM deleted)
                            THEN 'U'  -- SET ACTION TO UPDATED.
                        WHEN EXISTS(SELECT * FROM inserted)
                            THEN 'I'  -- SET ACTION TO INSERT.
                        WHEN EXISTS(SELECT * FROM deleted)
                            THEN 'D'  -- SET ACTION TO deleted.
                        ELSE NULL -- SKIP. IT MAY HAVE BEEN A "FAILED DELETE".   
                    END)
                    
     IF (@ACTION = 'I')
     BEGIN
        DECLARE @VARID INT
	    SELECT @VARID = (SELECT BIT_ID FROM inserted)
         
        INSERT INTO CONTROLB VALUES(@ACTION,USER_NAME(),@USER_MACHINE,GETDATE(),@VARID)
     END
     
     IF (@ACTION = 'U')
     BEGIN
        DECLARE C CURSOR  FOR
        SELECT BIT_ID
        FROM inserted

    	DECLARE @VARID1 INT

    	-- OPEN THE CURSOR
    	OPEN C

    	FETCH NEXT FROM C INTO @VARID1
    	WHILE (@@FETCH_STATUS = 0)
    	BEGIN
    		 INSERT INTO CONTROLB VALUES(@ACTION,USER_NAME(),@USER_MACHINE,GETDATE(),@VARID1)
    		-- DO OTHER STUFF

    		FETCH NEXT FROM C INTO @VARID1
    	END

    	-- CLOSE AND DEALLOCATE THE CURSOR
    	CLOSE C
    	DEALLOCATE C
     END
     
     
     IF (@ACTION = 'D')
     BEGIN
        DECLARE C CURSOR  FOR
        SELECT BIT_ID
        FROM deleted

    	DECLARE @VARID2 INT

    	-- OPEN THE CURSOR
    	OPEN C

    	FETCH NEXT FROM C INTO @VARID2
    	WHILE (@@FETCH_STATUS = 0)
    	BEGIN
    		 INSERT INTO CONTROLB VALUES(@ACTION,USER_NAME(),@USER_MACHINE,GETDATE(),@VARID2)
    		-- DO OTHER STUFF

    		FETCH NEXT FROM C INTO @VARID2
    	END

    	-- CLOSE AND DEALLOCATE THE CURSOR
    	CLOSE C
    	DEALLOCATE C
     END

GO


CREATE TRIGGER TR_ESTUDIANTE ON ESTUDIANTE
FOR INSERT, UPDATE, DELETE
AS
	--DETERMINE IF THIS IS AN INSERT,UPDATE, OR DELETE ACTION OR A "FAILED DELETE".
    DECLARE @ACTION CHAR(1)
    DECLARE @THIS_TABLE VARCHAR(20)
    DECLARE @USER_MACHINE VARCHAR(255)
    SELECT @THIS_TABLE = 'ESTUDIANTE'
    SELECT @USER_MACHINE = HOST_NAME()
    
    SET @ACTION = (CASE WHEN EXISTS(SELECT * FROM inserted)
                         AND EXISTS(SELECT * FROM deleted)
                            THEN 'U'  -- SET ACTION TO UPDATED.
                        WHEN EXISTS(SELECT * FROM inserted)
                            THEN 'I'  -- SET ACTION TO INSERT.
                        WHEN EXISTS(SELECT * FROM deleted)
                            THEN 'D'  -- SET ACTION TO deleted.
                        ELSE NULL -- SKIP. IT MAY HAVE BEEN A "FAILED DELETE".   
                    END)
                    
     IF (@ACTION = 'I')
     BEGIN
        DECLARE @VARID VARCHAR(12)
	    SELECT @VARID = (SELECT EST_ID FROM inserted)
        INSERT INTO BITACORA VALUES(@THIS_TABLE,@ACTION,USER_NAME(),@USER_MACHINE,GETDATE(),@VARID)
     END
     
     IF (@ACTION = 'U')
     BEGIN
        DECLARE C CURSOR  FOR
        SELECT EST_ID
        FROM inserted

    	DECLARE @VARID1 VARCHAR(12)

    	-- OPEN THE CURSOR
    	OPEN C

    	FETCH NEXT FROM C INTO @VARID1
    	WHILE (@@FETCH_STATUS = 0)
    	BEGIN
    		 INSERT INTO BITACORA VALUES(@THIS_TABLE,@ACTION,USER_NAME(),@USER_MACHINE,GETDATE(),@VARID1)

    		-- DO OTHER STUFF

    		FETCH NEXT FROM C INTO @VARID1
    	END

    	-- CLOSE AND DEALLOCATE THE CURSOR
    	CLOSE C
    	DEALLOCATE C
     END
     
     
     IF (@ACTION = 'D')
     BEGIN
        DECLARE C CURSOR  FOR
        SELECT EST_ID
        FROM deleted

    	DECLARE @VARID2 VARCHAR(12)

    	-- OPEN THE CURSOR
    	OPEN C

    	FETCH NEXT FROM C INTO @VARID2
    	WHILE (@@FETCH_STATUS = 0)
    	BEGIN
    		 INSERT INTO BITACORA VALUES(@THIS_TABLE,@ACTION,USER_NAME(),@USER_MACHINE,GETDATE(),@VARID2)

    		-- DO OTHER STUFF

    		FETCH NEXT FROM C INTO @VARID2
    	END

    	-- CLOSE AND DEALLOCATE THE CURSOR
    	CLOSE C
    	DEALLOCATE C
     END

GO


CREATE TRIGGER TR_INSCRIPCION ON INSCRIPCION
FOR INSERT, UPDATE, DELETE
AS
	--DETERMINE IF THIS IS AN INSERT,UPDATE, OR DELETE ACTION OR A "FAILED DELETE".
    DECLARE @ACTION CHAR(1)
    DECLARE @THIS_TABLE VARCHAR(20)
    DECLARE @USER_MACHINE VARCHAR(255)
    SELECT @THIS_TABLE = 'INSCRIPCION'
    SELECT @USER_MACHINE = HOST_NAME()
    
    SET @ACTION = (CASE WHEN EXISTS(SELECT * FROM inserted)
                         AND EXISTS(SELECT * FROM deleted)
                            THEN 'U'  -- SET ACTION TO UPDATED.
                        WHEN EXISTS(SELECT * FROM inserted)
                            THEN 'I'  -- SET ACTION TO INSERT.
                        WHEN EXISTS(SELECT * FROM deleted)
                            THEN 'D'  -- SET ACTION TO deleted.
                        ELSE NULL -- SKIP. IT MAY HAVE BEEN A "FAILED DELETE".   
                    END)
                    
     IF (@ACTION = 'I')
     BEGIN
        DECLARE @VARID VARCHAR(12)
	    SELECT @VARID = (SELECT INS_ID FROM inserted)
        INSERT INTO BITACORA VALUES(@THIS_TABLE,@ACTION,USER_NAME(),@USER_MACHINE,GETDATE(),@VARID)
     END
     
     IF (@ACTION = 'U')
     BEGIN
        DECLARE C CURSOR  FOR
        SELECT INS_ID
        FROM inserted

    	DECLARE @VARID1 VARCHAR(12)

    	-- OPEN THE CURSOR
    	OPEN C

    	FETCH NEXT FROM C INTO @VARID1
    	WHILE (@@FETCH_STATUS = 0)
    	BEGIN
    		 INSERT INTO BITACORA VALUES(@THIS_TABLE,@ACTION,USER_NAME(),@USER_MACHINE,GETDATE(),@VARID1)

    		-- DO OTHER STUFF

    		FETCH NEXT FROM C INTO @VARID1
    	END

    	-- CLOSE AND DEALLOCATE THE CURSOR
    	CLOSE C
    	DEALLOCATE C
     END
     
     
     IF (@ACTION = 'D')
     BEGIN
        DECLARE C CURSOR  FOR
        SELECT INS_ID
        FROM deleted

    	DECLARE @VARID2 VARCHAR(12)

    	-- OPEN THE CURSOR
    	OPEN C

    	FETCH NEXT FROM C INTO @VARID2
    	WHILE (@@FETCH_STATUS = 0)
    	BEGIN
    		 INSERT INTO BITACORA VALUES(@THIS_TABLE,@ACTION,USER_NAME(),@USER_MACHINE,GETDATE(),@VARID2)

    		-- DO OTHER STUFF

    		FETCH NEXT FROM C INTO @VARID2
    	END

    	-- CLOSE AND DEALLOCATE THE CURSOR
    	CLOSE C
    	DEALLOCATE C
     END

GO


CREATE TRIGGER TR_PROFESOR ON PROFESOR
FOR INSERT, UPDATE, DELETE
AS
	--DETERMINE IF THIS IS AN INSERT,UPDATE, OR DELETE ACTION OR A "FAILED DELETE".
    DECLARE @ACTION CHAR(1)
    DECLARE @THIS_TABLE VARCHAR(20)
    DECLARE @USER_MACHINE VARCHAR(255)
    SELECT @THIS_TABLE = 'PROFESOR'
    SELECT @USER_MACHINE = HOST_NAME()
    
    SET @ACTION = (CASE WHEN EXISTS(SELECT * FROM inserted)
                         AND EXISTS(SELECT * FROM deleted)
                            THEN 'U'  -- SET ACTION TO UPDATED.
                        WHEN EXISTS(SELECT * FROM inserted)
                            THEN 'I'  -- SET ACTION TO INSERT.
                        WHEN EXISTS(SELECT * FROM deleted)
                            THEN 'D'  -- SET ACTION TO deleted.
                        ELSE NULL -- SKIP. IT MAY HAVE BEEN A "FAILED DELETE".   
                    END)
                    
     IF (@ACTION = 'I')
     BEGIN
        DECLARE @VARID VARCHAR(12)
	    SELECT @VARID = (SELECT PRO_ID FROM inserted)
        INSERT INTO BITACORA VALUES(@THIS_TABLE,@ACTION,USER_NAME(),@USER_MACHINE,GETDATE(),@VARID)
     END
     
     IF (@ACTION = 'U')
     BEGIN
        DECLARE C CURSOR  FOR
        SELECT PRO_ID
        FROM inserted

    	DECLARE @VARID1 VARCHAR(12)

    	-- OPEN THE CURSOR
    	OPEN C

    	FETCH NEXT FROM C INTO @VARID1
    	WHILE (@@FETCH_STATUS = 0)
    	BEGIN
    		 INSERT INTO BITACORA VALUES(@THIS_TABLE,@ACTION,USER_NAME(),@USER_MACHINE,GETDATE(),@VARID1)

    		-- DO OTHER STUFF

    		FETCH NEXT FROM C INTO @VARID1
    	END

    	-- CLOSE AND DEALLOCATE THE CURSOR
    	CLOSE C
    	DEALLOCATE C
     END
     
     
     IF (@ACTION = 'D')
     BEGIN
        DECLARE C CURSOR  FOR
        SELECT PRO_ID
        FROM deleted

    	DECLARE @VARID2 VARCHAR(12)

    	-- OPEN THE CURSOR
    	OPEN C

    	FETCH NEXT FROM C INTO @VARID2
    	WHILE (@@FETCH_STATUS = 0)
    	BEGIN
    		 INSERT INTO BITACORA VALUES(@THIS_TABLE,@ACTION,USER_NAME(),@USER_MACHINE,GETDATE(),@VARID2)

    		-- DO OTHER STUFF

    		FETCH NEXT FROM C INTO @VARID2
    	END

    	-- CLOSE AND DEALLOCATE THE CURSOR
    	CLOSE C
    	DEALLOCATE C
     END

GO
